package skyinline.j2ee.basic.projectv5.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import skyinline.j2ee.basic.projectv5.model.BoardVO;
import skyinline.j2ee.basic.util.jdbc.MariaDB;

public class BoardDAO {
	private static String newSQL = 
		"insert into boardv4 (title, uid, contents) "
		+ "values (?,?,?)";
	private static String listSQL = 
		" select bdno, title, uid, wdate, reades"
		+ " from boardv4 order by bdno desc";
	private static String viewSQL = 
		"select * from boardv4 where bdno = ?";
	private static String updateSQL = 
		"update boardv4 set title=?, uid=?, contents=?"
		+ " where bdno = ?";
	private static String deleteSQL = 
		"delete from boardv4 where bdno = ?";	
	
	public static boolean newBoard( BoardVO b ) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = MariaDB.openConn();
			pstmt  = conn.prepareStatement(newSQL);
				pstmt.setString(1, b.getTitle());
				pstmt.setString(2, b.getUid());
				pstmt.setString(3, b.getContents());
			if ( pstmt.executeUpdate() > 0 ) result = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		
		return result;
	}
	
	public static List<BoardVO> listBoard() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVO> result = new ArrayList<>();
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(listSQL);
			rs = pstmt.executeQuery();
			while ( rs.next() ) {
				BoardVO b = new BoardVO();
					b.setBdno( rs.getString(1) );
					b.setTitle( rs.getString(2) );
					b.setUid( rs.getString(3) );
					b.setWdate( rs.getString(4) );
					b.setReades( rs.getString(5) );
				result.add(b);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, rs);
		}
		
		return result;
		
	}
	
	public static BoardVO viewBoard(BoardVO b) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardVO result = new BoardVO();
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(viewSQL);
				pstmt.setString(1, b.getBdno());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result.setBdno( rs.getString(1) );
				result.setTitle( rs.getString(2) );
				result.setUid( rs.getString(3) );
				result.setWdate( rs.getString(4) );
				result.setReades( rs.getString(5) );
				result.setContents( rs.getString(6) );
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static boolean updateBoard( BoardVO b ) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(updateSQL);
				pstmt.setString(1, b.getTitle());
				pstmt.setString(2, b.getUid());
				pstmt.setString(3, b.getContents());
				pstmt.setString(4, b.getBdno());
			if ( pstmt.executeUpdate() > 0 ) result = true; 	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		
		return result;		
	}
	
	public static boolean deleteBoard( BoardVO b ) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = MariaDB.openConn();
			pstmt = conn.prepareStatement(deleteSQL);
				pstmt.setString(1, b.getBdno());
			if ( pstmt.executeUpdate() > 0 ) result = true;	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MariaDB.closeConn(conn, pstmt, null);
		}
		
		return result;
		
	}
	
	
	
}




